﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.Common;

using ZhimeiSoft.CharmManager.DOL;
using ZhimeiSoft.Framework.Server.Base;
using ZhimeiSoft.CharmManager.Server.DAL.Interface;

namespace ZhimeiSoft.CharmManager.Server.DAL
{
    /// <summary>
    /// 代办事项
    /// </summary>
    public class ToDoListDAL : BaseDal, IToDoListDAL
    {

        public void InsertToDoList(DBEntityToDoList todoListInfo)
        {
            DbCommand cmdInsert = DB.GetSqlStringCommand(ToDoListDAL_Const.SQL_INSERT_TODOLIST);
            DB.AddInParameter(cmdInsert, DBEntityToDoList.FieldName.TODODECRIPTION, DbType.String, todoListInfo.TODODECRIPTION);
            DB.AddInParameter(cmdInsert, DBEntityToDoList.FieldName.TODOSTARTTIME, DbType.DateTime, todoListInfo.TODOSTARTTIME);
            DB.AddInParameter(cmdInsert, DBEntityToDoList.FieldName.TODOENDTIME, DbType.DateTime, todoListInfo.TODOENDTIME);
            DB.AddInParameter(cmdInsert, DBEntityToDoList.FieldName.TODOCOMPLETIONRATE, DbType.Int32, todoListInfo.TODOCOMPLETIONRATE);
            DB.AddInParameter(cmdInsert, DBEntityToDoList.FieldName.TODOSTATE, DbType.String, todoListInfo.TODOSTATE);
            DB.AddInParameter(cmdInsert, DBEntityToDoList.FieldName.TODOMEMO, DbType.String, todoListInfo.TODOMEMO);
            DB.AddInParameter(cmdInsert, DBEntityToDoList.FieldName.EMPLOYEEID, DbType.Int32, todoListInfo.EMPLOYEEID);
            DB.ExecuteNonQuery(cmdInsert);
        }

        public void InsertResverationToDoList(DBEntityToDoList todoListInfo, DBEntityReservationToDoList reservationToDoListInfo)
        {
            //插入代办事项
            InsertToDoList(todoListInfo);
            //获取代办事项ID
            GetToDoListID(todoListInfo);
            //插入待办事项和预约关联
            reservationToDoListInfo.TODOLISTID = todoListInfo.TODOLISTID;
            DbCommand cmdInsert = DB.GetSqlStringCommand(ToDoListDAL_Const.SQL_INSERT_RESVERATIONTODOLIST);
            DB.AddInParameter(cmdInsert, DBEntityReservationToDoList.FieldName.TODOLISTID, DbType.Int32, reservationToDoListInfo.TODOLISTID);
            DB.AddInParameter(cmdInsert, DBEntityReservationToDoList.FieldName.RESERVATIONID, DbType.Int32, reservationToDoListInfo.RESERVATIONID);
            DB.ExecuteNonQuery(cmdInsert);
        }
        /// <summary>
        /// 获取待办事项ＩＤ
        /// </summary>
        /// <param name="todoList"></param>
        private void GetToDoListID(DBEntityToDoList todoListInfo)
        {
            DbCommand cmdQuery = DB.GetSqlStringCommand(ToDoListDAL_Const.SQL_QUERY_TODOLISTID);
            DB.AddInParameter(cmdQuery, DBEntityToDoList.FieldName.TODODECRIPTION, DbType.String, todoListInfo.TODODECRIPTION);
            DB.AddInParameter(cmdQuery, DBEntityToDoList.FieldName.TODOSTARTTIME, DbType.DateTime, todoListInfo.TODOSTARTTIME);
            DB.AddInParameter(cmdQuery, DBEntityToDoList.FieldName.TODOENDTIME, DbType.DateTime, todoListInfo.TODOENDTIME);
            DB.AddInParameter(cmdQuery, DBEntityToDoList.FieldName.TODOCOMPLETIONRATE, DbType.Int32, todoListInfo.TODOCOMPLETIONRATE);
            DB.AddInParameter(cmdQuery, DBEntityToDoList.FieldName.TODOSTATE, DbType.String, todoListInfo.TODOSTATE);
            DB.AddInParameter(cmdQuery, DBEntityToDoList.FieldName.TODOMEMO, DbType.String, todoListInfo.TODOMEMO);
            DB.AddInParameter(cmdQuery, DBEntityToDoList.FieldName.EMPLOYEEID, DbType.Int32, todoListInfo.EMPLOYEEID);
            DataSet dsToDoList = DB.ExecuteDataSet(cmdQuery);

            if (dsToDoList.Tables[0].Rows.Count == 0)
            {
                throw new Exception("没有找到待办事项ID");
            }
            todoListInfo.TODOLISTID = Convert.ToInt32(dsToDoList.Tables[0].Rows[0][DBEntityToDoList.FieldName.TODOLISTID]);

        }

        public void UpdateToDoList(DBEntityToDoList todoListInfo)
        {
            DbCommand cmdUpdate = DB.GetSqlStringCommand(ToDoListDAL_Const.SQL_UPDATE_TODOLIST);
            DB.AddInParameter(cmdUpdate, DBEntityToDoList.FieldName.TODODECRIPTION, DbType.String, todoListInfo.TODODECRIPTION);
            DB.AddInParameter(cmdUpdate, DBEntityToDoList.FieldName.TODOSTARTTIME, DbType.DateTime, todoListInfo.TODOSTARTTIME);
            DB.AddInParameter(cmdUpdate, DBEntityToDoList.FieldName.TODOENDTIME, DbType.DateTime, todoListInfo.TODOENDTIME);
            DB.AddInParameter(cmdUpdate, DBEntityToDoList.FieldName.TODOCOMPLETIONRATE, DbType.Int32, todoListInfo.TODOCOMPLETIONRATE);
            DB.AddInParameter(cmdUpdate, DBEntityToDoList.FieldName.TODOSTATE, DbType.String, todoListInfo.TODOSTATE);
            DB.AddInParameter(cmdUpdate, DBEntityToDoList.FieldName.TODOMEMO, DbType.String, todoListInfo.TODOMEMO);
            DB.AddInParameter(cmdUpdate, DBEntityToDoList.FieldName.EMPLOYEEID, DbType.Int32, todoListInfo.EMPLOYEEID);
            DB.AddInParameter(cmdUpdate, DBEntityToDoList.FieldName.TODOLISTID, DbType.Int32, todoListInfo.TODOLISTID);
            DB.ExecuteNonQuery(cmdUpdate);
        }

        public void DeleteToDoList(int todoListID)
        {
            DbCommand cmdDelete = DB.GetSqlStringCommand(ToDoListDAL_Const.SQL_DELETE_TODOLIST);
            DB.AddInParameter(cmdDelete, DBEntityToDoList.FieldName.TODOLISTID, DbType.Int32, todoListID);
            DB.ExecuteNonQuery(cmdDelete);
        }
        /// <summary>
        /// 根据预约ID查询对应的提醒ID
        /// </summary>
        /// <param name="reservationID">预约ID</param>
        /// <returns></returns>
        public DataSet GetToDoListReservation(int reservationID)
        {
            DbCommand cmd = DB.GetSqlStringCommand(ToDoListDAL_Const.SQL_QUERY_RESVERATIONTODOLIST);
            DB.AddInParameter(cmd, DBEntityReservationToDoList.FieldName.RESERVATIONID, DbType.Int32, reservationID);
            return DB.ExecuteDataSet(cmd);
        }

        /// <summary>
        /// 根据ID获取代码事项
        /// </summary>
        /// <param name="todoListID"></param>
        /// <returns></returns>
        public DataSet GetToDoListForID(int todoListID)
        {
            DbCommand cmd = DB.GetSqlStringCommand(ToDoListDAL_Const.SQL_QUERY_TODOLIST_TODOLISTID);
            DB.AddInParameter(cmd, DBEntityToDoList.FieldName.TODOLISTID, DbType.Int16, todoListID);
            return DB.ExecuteDataSet(cmd);
        }

        public void DeleteResverationToDoList(DBEntityReservationToDoList reservationToDoListInfo)
        {
            DbCommand cmdDelete = DB.GetSqlStringCommand(ToDoListDAL_Const.SQL_DELETE_RESVERATIONTODOLIST);
            DB.AddInParameter(cmdDelete, DBEntityReservationToDoList.FieldName.TODOLISTID, DbType.Int32, reservationToDoListInfo.TODOLISTID);
            DB.AddInParameter(cmdDelete, DBEntityReservationToDoList.FieldName.RESERVATIONID, DbType.Int32, reservationToDoListInfo.RESERVATIONID);
            DB.ExecuteNonQuery(cmdDelete);
        }
        /// <summary>
        /// 根据时间状态获取代办事项
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public DataSet GetStateToDoList(DateTime startDate, string state)
        {
            DbCommand cmdQuery;
            if(state.Equals(string.Empty)){
                cmdQuery = DB.GetSqlStringCommand(ToDoListDAL_Const.SQL_QUWEY_TODOLIST_STARTTIME);
            }else{
                cmdQuery = DB.GetSqlStringCommand(ToDoListDAL_Const.SQL_QUWEY_TODOLIST_STARTTIME_STATE);
                DB.AddInParameter(cmdQuery, DBEntityToDoList.FieldName.TODOSTATE, DbType.String, state);
            }
            DB.AddInParameter(cmdQuery, "STARTYEAR", DbType.Int32, startDate.Year);
            DB.AddInParameter(cmdQuery, "STARTMONTH", DbType.Int32, startDate.Month);
            DB.AddInParameter(cmdQuery, "STARTDAY", DbType.Int32, startDate.Day);
            return DB.ExecuteDataSet(cmdQuery);
        }

        /// <summary>
        /// 根据过滤条件获取待办事项
        /// </summary>
        /// <param name="sFilter">过滤条件</param>
        /// <returns></returns>
        public DataSet QueryTodoList(string sFilter)
        {
            string strSQL = ToDoListDAL_Const.SQL_QUERY_ALLTODOLIST;
            if (sFilter != string.Empty)
                strSQL += " AND " + sFilter;

            DbCommand cmd = DB.GetSqlStringCommand(strSQL);
            return DB.ExecuteDataSet(cmd);
        }

        public void MarkCompleted(int todoListID)
        {
            string strSQL = ToDoListDAL_Const.SQL_UPDATE_MARKCOMPLETED;
            DbCommand cmd = DB.GetSqlStringCommand(strSQL);
            DB.AddInParameter(cmd, DBEntityToDoList.FieldName.TODOLISTID, DbType.Int32, todoListID);
            DB.ExecuteNonQuery(cmd);
        }

        public void CheckAllExpiredTodoState()
        {
            string strSQL = ToDoListDAL_Const.SQL_UPDATE_UPDATEEXPIREDTODOSTATE;
            DbCommand cmd = DB.GetSqlStringCommand(strSQL);
            DB.ExecuteDataSet(cmd);

            strSQL = ToDoListDAL_Const.SQL_UPDATE_UPDATEEXPIREDTODOSTATE_STARTNOW;
            cmd = DB.GetSqlStringCommand(strSQL);
            DB.ExecuteDataSet(cmd);
        }
        /// <summary>
        /// 根据todolistID查询预约
        /// </summary>
        /// <param name="todoListID"></param>
        /// <returns></returns>
        public DataSet QueryReservationForToDoListID(int todoListID)
        {
            DbCommand cmd = DB.GetSqlStringCommand(ToDoListDAL_Const.SQL_QUERY_RESVERATIONTODOLIST_RESRVATION);
            DB.AddInParameter(cmd, DBEntityToDoList.FieldName.TODOLISTID, DbType.Int16, todoListID);
            return DB.ExecuteDataSet(cmd);
        }
    }
}
